# ==============================
# _*_ coding:utf-8 _*_
# _*_ by:zizle     _*_
# _*_ at:20230423  _*_
# ==============================
import json

from pymysql.cursors import DictCursor
from redis import Redis
from .pools import base_db_pool, index_db_pool, department_db_pool, redis_pool


class BaseConnection:
    def __init__(self):
        self.conn = None
        self.cursor = None

    def begin(self):
        self.conn.begin()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def __enter__(self):
        self.begin()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.rollback()
            # 全局处理错误了
            # Logger().fetal(fetal='MySQL Error: {}:{}'.format(exc_type.__name__, exc_val))
        else:
            self.commit()
        self.close()

    def query_paginator(self, sql: str, param: list, page: int, page_size: int) -> dict:
        sql = sql.replace('SELECT', 'SELECT SQL_CALC_FOUND_ROWS ', 1)
        sql = sql.replace(';', ' LIMIT %s,%s;')
        param.append((page - 1) * page_size)
        param.append(page_size)
        ret = {'rows': [], 'total_count': 0, 'total_page': 1, 'page': 1, 'page_size': page_size}
        # 查询记录
        self.cursor.execute(sql, param)
        ret['rows'] = list(self.cursor.fetchall())
        # 查询总数
        self.cursor.execute('SELECT FOUND_ROWS() AS total;')
        total = self.cursor.fetchone()
        ret['page'] = page
        if total:
            ret['total_count'] = total['total'] if total else 0
            ret['total_page'] = (ret['total_count'] + page_size - 1) // page_size
        return ret


class BaseDatabaseConnection(BaseConnection):
    def __init__(self):
        super().__init__()
        self.conn = base_db_pool.connection()
        self.cursor = self.conn.cursor(DictCursor)


class IndexDataBaseConnection(BaseConnection):
    def __init__(self, db_name):
        super().__init__()
        self.conn = index_db_pool.connection()
        self.cursor = self.conn.cursor(DictCursor)
        self.cursor.execute("USE `{0}`;".format(db_name))

    def create_table(self, table_name):
        sql = """
            CREATE TABLE IF NOT EXISTS `{}`(
                `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
                `datadate` DATE UNIQUE NOT NULL,
                `datavalue` DOUBLE(16,4) NOT NULL,
                `is_active` BIT NOT NULL DEFAULT 1
            ) DEFAULT CHARSET=utf8mb4;
        """.format(table_name)
        self.cursor.execute(sql)

    def query_with_interval(self, tb_name, start_date, end_date, ascending=True, is_all=False):
        sql = """
            SELECT id,datadate,datavalue FROM `{0}` 
            WHERE is_active=1 AND datadate>='{1}' AND datadate<='{2}' ORDER BY datadate {3};
        """.format(tb_name, start_date, end_date, 'ASC' if ascending else 'DESC')
        if is_all:
            sql = """
                SELECT id,datadate,datavalue,is_active FROM `{0}` 
                WHERE datadate>='{1}' AND datadate<='{2}' ORDER BY datadate {3};
            """.format(tb_name, start_date, end_date, 'ASC' if ascending else 'DESC')

        self.cursor.execute(sql)
        rows = list(self.cursor.fetchall())
        value_start, value_end = (None, None)
        if len(rows) > 0 and ascending:
            value_start = rows[0]['datadate']
            value_end = rows[-1]['datadate']
        if len(rows) > 0 and not ascending:
            value_start = rows[-1]['datadate']
            value_end = rows[0]['datadate']
        return {
            'rows': rows,
            'start_date': value_start,
            'end_date': value_end
        }

    def query_with_paginator(self, tb_name, page, page_size, ascending=True, is_all=False):
        sql = """
            SELECT id,datadate,datavalue FROM `{0}` WHERE is_active=1 ORDER BY datadate {1};
        """.format(tb_name, 'ASC' if ascending else 'DESC')
        if is_all:
            sql = """
                SELECT id,datadate,datavalue,is_active FROM `{0}` ORDER BY datadate {1};
            """.format(tb_name, 'ASC' if ascending else 'DESC')

        ret = self.query_paginator(sql, param=[], page=page, page_size=page_size)
        count = len(ret['rows'])
        value_start, value_end = (None, None)
        if count and ascending:
            value_start = ret['rows'][0]['datadate']
            value_end = ret['rows'][-1]['datadate']
        if count and not ascending:
            value_start = ret['rows'][-1]['datadate']
            value_end = ret['rows'][0]['datadate']
        ret['start_date'] = value_start
        ret['end_date'] = value_end
        return ret


class DepartmentDataBaseConnection(BaseConnection):
    def __init__(self):
        super().__init__()
        self.conn = department_db_pool.connection()
        self.cursor = self.conn.cursor(DictCursor)


# redis
class RedisConnection:
    def __init__(self):
        self.conn = Redis(connection_pool=redis_pool)

    def __enter__(self):
        return self

    def __exit__(self, exc, value, traceback):
        self.conn.close()

    def close(self):
        self.conn.close()

    def set_value(self, key, value, expires=None):
        # expires为秒
        self.conn.set(key, value, ex=expires)

    def get_value(self, key):
        return self.conn.get(key)

    def set_variety_names(self, v_dict: dict):
        self.set_value(key='ruizhi_variety', value=json.dumps(v_dict))

    def get_variety_names(self, key=None):
        data_str = self.get_value(key='ruizhi_variety')
        if not data_str:
            return '' if key else {}
        else:
            v_data = json.loads(data_str)
            return v_data.get(key, '') if key else v_data
